﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using kwzx.Utils;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Text.RegularExpressions;
using System.IO;
using System.Threading;

namespace kwzx.ImpExcel
{
    public partial class ExcelOper : Form
    {
        DataSet1TableAdapters.T_USERTableAdapter useradp = new DataSet1TableAdapters.T_USERTableAdapter();
        DataSet1TableAdapters.T_GROUPTableAdapter groupadp = new DataSet1TableAdapters.T_GROUPTableAdapter();
        DataSet1TableAdapters.T_BMTableAdapter bmadp = new DataSet1TableAdapters.T_BMTableAdapter();
        DataSet1TableAdapters.X_CS_DETAILTableAdapter detailadp = new DataSet1TableAdapters.X_CS_DETAILTableAdapter();
        DataSet1TableAdapters.CD_UserTypeTableAdapter usertypeadp = new DataSet1TableAdapters.CD_UserTypeTableAdapter();

        Utils.CommonData commdata = new CommonData();
        string pathName = System.Windows.Forms.Application.StartupPath + "/zp/";
        string pathFileName = "";

        int gsdwid = -2;//公司ID
        int gsbmid = -2;//归属部门ID
        int rylbid = -2;//人员ID
        int gwgzid = -2;//工种ID
        int gsdeptid = -2;//公司对应的部门ID
        string zp = "";//照片存放路径
        string gsdw = "";//公司单位全称
        string tempbz = "数据不正确";//数据验证标识
        string _excelOper = "";

        /// <summary>
        /// 无参构造函数
        /// </summary>
        public ExcelOper(string excelOper)
        {
            _excelOper = excelOper;
            InitializeComponent();
        }

        /// <summary>
        /// Form初始加载操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ExcelOper_Load(object sender, EventArgs e)
        {
            //当照片存放路径为空时，默认的路径为当前程序下的images文件夹
            this.textBox2.Text = System.Windows.Forms.Application.StartupPath + "/zp/";
            this.textBox1.Text = System.Windows.Forms.Application.StartupPath + "/zp/非正式员工Excel模版.xls";
            this.FormBorderStyle = FormBorderStyle.FixedSingle;//不允许拉申窗口大小
            this.MaximizeBox = false;//不允许最大化

            //判断是否显示“检验人员和照片是否对应”按钮
            if ((this.textBox1.Text.Trim() != "") || (this.textBox2.Text.Trim() != ""))
            {
                this.button4.Enabled = true;
            }
        }

        /// <summary>
        /// 选择照片存放路径，默认的路径为当前程序下的images里面
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            if (this.folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                this.label4.Text = "照片选择路径为：";
                this.textBox2.Text = this.folderBrowserDialog1.SelectedPath;
            }
            else
            {
                this.label4.Text = "照片默认存放路径为：";
                this.textBox2.Text = System.Windows.Forms.Application.StartupPath + "/zp/";
            }
        }

        /// <summary>
        /// 设置照片存放路径为当前程序下的images文件夹
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            this.label4.Text = "照片默认存放路径为：";
            this.textBox2.Text = System.Windows.Forms.Application.StartupPath + "/zp/";
        }

        /// <summary>
        /// 加载Excel文件，验证及入库功能
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string FileName = this.openFileDialog1.FileName;
                this.textBox1.Text = FileName;

                //加载Excel文件到DataTable中
                System.Data.DataTable tb = JZExcelFile();

                if (tb != null)
                {
                    string filePath = textBox1.Text;
                    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                    app.Visible = false;

                    //打开Exce
                    Workbook w = (Workbook)app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                    Microsoft.Office.Interop.Excel._Worksheet xSt = (Microsoft.Office.Interop.Excel._Worksheet)w.Worksheets[1];

                    try
                    {
                        for (int i = 0; i < tb.Rows.Count; i++)
                        {
                            int j = i;//用来标识Excel中和记录是第几条
                            int xfzt = 0;
                            tempbz = "数据不正确";//数据验证标识

                            //必填数据
                            string yggh = tb.Rows[i]["员工工号"].ToString();
                            string xm = Regex.Replace(tb.Rows[i]["姓名"].ToString(), @"\s", "");
                            string xb = Regex.Replace(tb.Rows[i]["性别"].ToString(), @"\s", "");
                            string sfzhm = Convert.ToString(Regex.Replace(tb.Rows[i]["身份证号码"].ToString(), @"\s", ""));
                            string gsdwqc = Regex.Replace(tb.Rows[i]["公司单位全称"].ToString(), @"\s", "");
                            string gsbm = Regex.Replace(tb.Rows[i]["归属部门"].ToString(), @"\s", "");
                            string rylb = Regex.Replace(tb.Rows[i]["人员类别"].ToString(), @"\s", "");
                            string gwgz = Regex.Replace(tb.Rows[i]["岗位工种"].ToString(), @"\s", "");
                            gsdw = gsdwqc;

                            //验证能通过就可以的数据
                            string zgzzc = tb.Rows[i]["资格证职称"].ToString();
                            string byyx = tb.Rows[i]["毕业院校"].ToString();
                            string xl = tb.Rows[i]["学历"].ToString();
                            string tsgzzjhm = tb.Rows[i]["特殊工种证件号码"].ToString();
                            string dh = Regex.Replace(tb.Rows[i]["电话"].ToString(), @"\s", "");
                            string sj = Regex.Replace(tb.Rows[i]["手机"].ToString(), @"\s", "");
                            string bz = tb.Rows[i]["备注"].ToString();
                            DateTime sr = DateTime.Parse("1900-01-01");
                            DateTime tjdate = DateTime.Now;

                            int xh = 0;
                            try { xh = tb.Rows[i]["序号"] != null ? int.Parse(tb.Rows[i]["序号"].ToString()) : 0; }
                            catch (Exception ex) { xh = 0; }

                            DateTime byrq = DateTime.Parse("1900-01-01");
                            try { byrq = tb.Rows[i]["毕业日期"] != null ? DateTime.Parse(tb.Rows[i]["毕业日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { byrq = DateTime.Parse("1900-01-01"); }

                            DateTime rzrq = DateTime.Parse("1900-01-01");
                            try { rzrq = tb.Rows[i]["入职日期"] != null ? DateTime.Parse(tb.Rows[i]["入职日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { rzrq = DateTime.Parse("1900-01-01"); }

                            DateTime tsgzzjyxq = DateTime.Parse("1900-01-01");
                            try { tsgzzjyxq = tb.Rows[i]["特殊工种证件有效期"] != null ? DateTime.Parse(tb.Rows[i]["特殊工种证件有效期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { tsgzzjyxq = DateTime.Parse("1900-01-01"); }

                            DateTime tsgzzjfsrq = DateTime.Parse("1900-01-01");
                            try { tsgzzjfsrq = tb.Rows[i]["特殊工种证件复审日期"] != null ? DateTime.Parse(tb.Rows[i]["特殊工种证件复审日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { tsgzzjfsrq = DateTime.Parse("1900-01-01"); }

                            DateTime bzrq = DateTime.Parse("1900-01-01");
                            try { bzrq = tb.Rows[i]["办证日期"] != null ? DateTime.Parse(tb.Rows[i]["办证日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { bzrq = DateTime.Parse("1900-01-01"); }

                            //必填项，数据验证
                            tempbz = YZXX(xm, xb, sfzhm, gsdwqc, gsbm, rylb, gwgz);

                            if (bz.Contains("已入库"))
                            {
                                //已入库的记录，目前不用处理
                            }
                            else
                            {
                                //判断提交的Excel表格中是不是有重复的身份证号码
                                int sfznum = tb.Select("身份证号码 = '" + sfzhm + "'").Count();
                                if (sfznum < 2)
                                {
                                    //Excel中只有唯一的一条记录时
                                    if (tempbz == "数据不正确")//表示验证通过
                                    {
                                        //去数据库查询，该身份证号是否存在，若身份证匹配，就进行下一条记录
                                        System.Data.DataTable dt = useradp.GetDataBy_sfzh(sfzhm);
                                        if (dt.Rows.Count < 1)
                                        {
                                            //身份证无匹配信息，就新增一条记录
                                            //获取人员编号
                                            string rybh = commdata.getRybh(gsdwqc, gwgz);

                                            //数据通过合法的验证，可以在T_USER表插入数据
                                            useradp.Insert(gsdeptid, xm, xm, xh, "全局访问", xb, sr, dh, "123456", "", gsdwqc, "", "", zp, "不停用", 0, gsbmid + "", rylbid, sfzhm, rybh, tjdate, gwgzid, 200, 0, "", "卡加指纹", "-100", "", byyx, byrq, xl, rzrq, tsgzzjhm, tsgzzjyxq, tsgzzjfsrq, bzrq, sj, xfzt);

                                            //记录插入成功时，更新Excel“备注”字段提示：数据合格：已入库
                                            xSt.Cells[j + 2, 20] = "数据合格：已入库(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                            //合格记录从1到21列，全部都是黄绿底，黑字
                                            Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                            range.Interior.ColorIndex = 35;//背景的颜色：黄绿色
                                            range.Font.ColorIndex = 1;//字的颜色：黑色

                                            //日志记录
                                            commdata.WriteLogFile("1（数据合格入库）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                        }
                                        else
                                        {
                                            //身份证已查到，但是备注字段没有标识“已入库”，需要去数据库中查一下，从而判断该记录是不是已操作过
                                            System.Data.DataTable szfhhasmore = useradp.GetDataBy_XM_SFZH(xm, sfzhm);
                                            if (szfhhasmore.Rows.Count == 1)
                                            {
                                                //用Excel中的姓名和身份证号去数据库查，有唯一记录时，更新Excel“备注”字段提示：身份证号系统已经存在，请勿重复添加
                                                xSt.Cells[j + 2, 20] = "身份证号已经存在，请勿重复添加(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                                //不合格记录从1到21列，全部都是白底红字
                                                Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                                range.Interior.ColorIndex = 2;//背景的颜色：白色
                                                range.Font.ColorIndex = 3;//字的颜色：红色

                                                //日志记录
                                                commdata.WriteLogFile("2（身份证号已经存在）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                            }
                                            else
                                            {
                                                //用Excel中的姓名和身份证号去数据库查，有多条记录时，更新Excel“备注”字段提示：身份证号与他人重复，请核对信息
                                                //验证未通过，有不合格数据，不合格信息写在“备注”字段内
                                                xSt.Cells[j + 2, 20] = "身份证号与他人重复，请核对信息(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                                //不合格记录从1到21列，全部都是白底红字
                                                Excel.Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                                range.Interior.ColorIndex = 2;//背景的颜色：白色
                                                range.Font.ColorIndex = 3;//字的颜色：红色

                                                //日志记录
                                                commdata.WriteLogFile("3（身份证号与他人重复）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                            }
                                        }
                                    }
                                    else
                                    {
                                        //验证未通过，有不合格数据，不合格信息写在“备注”字段内
                                        xSt.Cells[j + 2, 20] = tempbz;

                                        //不合格记录从1到21列，全部都是白底红字
                                        Excel.Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                        range.Interior.ColorIndex = 2;//背景的颜色：白色
                                        range.Font.ColorIndex = 3;//字的颜色：红色

                                        //日志记录
                                        commdata.WriteLogFile("4（未通过验证）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                    }
                                }
                                else
                                {
                                    //Excel中有重复的身份证记录，更新Excel文件“备注”字段内容，数据不进库
                                    xSt.Cells[j + 2, 20] = tempbz + " Excel内身份证号重复（需要重新提交身份证号及照片信息）(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ") ";//2005-11-5 14:06 

                                    //不合格记录从1到21列，全部都是白底红字
                                    Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                    range.Interior.ColorIndex = 2;//背景的颜色：白色
                                    range.Font.ColorIndex = 3;//字的颜色：红色

                                    //日志记录
                                    commdata.WriteLogFile("5（Excel内身份证号重复）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                }
                            }
                        }
                        w.Save();
                        //退出excel
                        app.Quit();
                        app = null;
                    }
                    catch (Exception ex)
                    {
                        //退出excel
                        app.Quit();
                        app = null;
                    }
                }
            }
        }


        /// <summary>
        /// 加载Excel文件,返回DataTable
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable JZExcelFile()
        {
            if (this.textBox1.Text.Trim() != "")
            {
                try
                {
                    //返回值
                    System.Data.DataTable dt = null;
                    //获得文件名
                    string path = this.textBox1.Text.Trim().ToString();
                    //创建一个数据链接 
                    string strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + " ;Extended Properties='Excel 8.0;HDR=False;IMEX=1;'";
                    OleDbConnection myConn = new OleDbConnection(strCon);
                    // string strCom = " SELECT 人员编号,姓名,性别,电话,身份证号,所属部门,公司,照片,工种 FROM [Sheet1$] ";
                    string strCom = " SELECT 员工工号,姓名,性别,身份证号码,公司单位全称,归属部门,人员类别,岗位工种,资格证职称,毕业院校,毕业日期,学历,入职日期,特殊工种证件号码,特殊工种证件有效期,特殊工种证件复审日期,办证日期,电话,手机,备注,序号 FROM [Sheet1$] ";
                    myConn.Open();
                    //打开数据链接，得到一个数据集 d
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                    //创建一个 DataSet对象 
                    DataSet myDataSet = new DataSet();
                    //得到自己的DataSet对象 
                    myCommand.Fill(myDataSet, "[Sheet1$]");

                    //关闭此数据链接 
                    myConn.Close();
                    dt = myDataSet.Tables[0];
                    return dt;
                }
                catch (Exception ex)
                {

                    MessageBox.Show("Excel文件表头字段不对！");
                    return null;
                }
            }
            else
            {
                MessageBox.Show("请先选择一个Excel文件！");
                return null;
            }
        }
        
        //用线程去处理Excel导入+进度显示
        private void ThreadTask()
        {
            int newval;
            string FileName = this.openFileDialog1.FileName;
            this.textBox1.Text = FileName;

            //循环Excel文件，去照片目录下读取照片信息。要是成功匹配，修改Excel第21列数据。不成功提示。
            //加载Excel文件到DataTable中
            System.Data.DataTable tb = JZExcelFile();

            if (tb != null)
            {
                string filePath = textBox1.Text;
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;

                //打开Exce
                Workbook w = (Workbook)app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value,
                     Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                     Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                Microsoft.Office.Interop.Excel._Worksheet xSt = (Microsoft.Office.Interop.Excel._Worksheet)w.Worksheets[1];

                try
                {
                    for (int i = 0; i < tb.Rows.Count; i++)
                    {
                        this.progressBar1.Maximum = tb.Rows.Count;
                        newval = this.progressBar1.Value + tb.Rows.Count;

                        if (newval > this.progressBar1.Maximum)
                            newval = this.progressBar1.Maximum;
                        else if (newval < this.progressBar1.Minimum)
                            newval = this.progressBar1.Minimum;

                        this.progressBar1.Value = newval;

                        int j = i;//用来标识Excel中和记录是第几条
                        int xfzt = 0;
                        tempbz = "数据不正确";//数据验证标识

                        //必填数据
                        string yggh = tb.Rows[i]["员工工号"].ToString();
                        string xm = Regex.Replace(tb.Rows[i]["姓名"].ToString(), @"\s", "");
                        string xb = Regex.Replace(tb.Rows[i]["性别"].ToString(), @"\s", "");
                        string sfzhm = Convert.ToString(Regex.Replace(tb.Rows[i]["身份证号码"].ToString(), @"\s", ""));
                        string gsdwqc = Regex.Replace(tb.Rows[i]["公司单位全称"].ToString(), @"\s", "");
                        string gsbm = Regex.Replace(tb.Rows[i]["归属部门"].ToString(), @"\s", "");
                        string rylb = Regex.Replace(tb.Rows[i]["人员类别"].ToString(), @"\s", "");
                        string gwgz = Regex.Replace(tb.Rows[i]["岗位工种"].ToString(), @"\s", "");
                        gsdw = gsdwqc;

                        //验证能通过就可以的数据
                        string zgzzc = tb.Rows[i]["资格证职称"].ToString();
                        string byyx = tb.Rows[i]["毕业院校"].ToString();
                        string xl = tb.Rows[i]["学历"].ToString();
                        string tsgzzjhm = tb.Rows[i]["特殊工种证件号码"].ToString();
                        string dh = Regex.Replace(tb.Rows[i]["电话"].ToString(), @"\s", "");
                        string sj = Regex.Replace(tb.Rows[i]["手机"].ToString(), @"\s", "");
                        string bz = tb.Rows[i]["备注"].ToString();
                        DateTime sr = DateTime.Parse("1900-01-01");
                        DateTime tjdate = DateTime.Now;

                        int xh = 0;
                        try { xh = tb.Rows[i]["序号"] != null ? int.Parse(tb.Rows[i]["序号"].ToString()) : 0; }
                        catch (Exception ex) { xh = 0; }

                        DateTime byrq = DateTime.Parse("1900-01-01");
                        try { byrq = tb.Rows[i]["毕业日期"] != null ? DateTime.Parse(tb.Rows[i]["毕业日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                        catch (Exception ex) { byrq = DateTime.Parse("1900-01-01"); }

                        DateTime rzrq = DateTime.Parse("1900-01-01");
                        try { rzrq = tb.Rows[i]["入职日期"] != null ? DateTime.Parse(tb.Rows[i]["入职日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                        catch (Exception ex) { rzrq = DateTime.Parse("1900-01-01"); }

                        DateTime tsgzzjyxq = DateTime.Parse("1900-01-01");
                        try { tsgzzjyxq = tb.Rows[i]["特殊工种证件有效期"] != null ? DateTime.Parse(tb.Rows[i]["特殊工种证件有效期"].ToString()) : DateTime.Parse("1900-01-01"); }
                        catch (Exception ex) { tsgzzjyxq = DateTime.Parse("1900-01-01"); }

                        DateTime tsgzzjfsrq = DateTime.Parse("1900-01-01");
                        try { tsgzzjfsrq = tb.Rows[i]["特殊工种证件复审日期"] != null ? DateTime.Parse(tb.Rows[i]["特殊工种证件复审日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                        catch (Exception ex) { tsgzzjfsrq = DateTime.Parse("1900-01-01"); }

                        DateTime bzrq = DateTime.Parse("1900-01-01");
                        try { bzrq = tb.Rows[i]["办证日期"] != null ? DateTime.Parse(tb.Rows[i]["办证日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                        catch (Exception ex) { bzrq = DateTime.Parse("1900-01-01"); }

                        //必填项，数据开始验证
                        tempbz = YZXX(xm, xb, sfzhm, gsdwqc, gsbm, rylb, gwgz);

                        if (this.textBox2.Text.Trim() != "")
                            pathName = this.textBox2.Text.Trim();
                        pathFileName = xm + sfzhm + ".jpg";
                        FileInfo fileinfo = new FileInfo(pathName + "/" + pathFileName);

                        if (bz.Contains("已入库"))
                        {
                            //已入库的记录，目前不用处理
                        }
                        else
                        {
                            //信息验证通过，有对应照片，数据库中没有身份证方可以通过，入库
                            //判断提交的Excel表格中是不是有重复的身份证号码
                            int sfznum = tb.Select("身份证号码 = '" + sfzhm + "'").Count();
                            if (sfznum < 2)
                            {
                                //Excel中只有唯一的一条记录时
                                if (tempbz == "数据不正确")//表示验证通过
                                {
                                    //去数据库查询，该身份证号是否存在，若身份证匹配，就进行下一条记录，不存在就新增一条
                                    System.Data.DataTable dt = useradp.GetDataBy_sfzh(sfzhm);
                                    if (dt.Rows.Count < 1)
                                    {
                                        //身份证无匹配信息，就新增一条记录
                                        //先做FTP上传照片文件,再获取人员编号，之后插入数据库记录
                                        int aa = Common.FileTran.SaveFile(fileinfo, pathFileName, "DBFZSPersonImg", "");
                                        if (!(aa == 0))
                                        {
                                            MessageBox.Show("FTP服务器连接失败，请检查网络连接！");
                                            commdata.WriteLogFile("\tFTP服务器连接失败，请检查网络连接！");

                                            w.Save();
                                            w.Close(Missing.Value, Missing.Value, Missing.Value);
                                            app.Workbooks.Close();

                                            app.Quit();
                                            app = null;
                                            return;
                                        }
                                        //获取人员编号
                                        string rybh = commdata.getRybh(gsdwqc, gwgz);

                                        //数据通过合法的验证，可以在T_USER表插入数据
                                        useradp.Insert(gsdeptid, xm, xm, xh, "全局访问", xb, sr, dh, "123456", "", gsdwqc, "", "", zp, "不停用", 0, gsbmid + "", rylbid, sfzhm, rybh, tjdate, gwgzid, 200, 0, "", "卡加指纹", "-100", "", byyx, byrq, xl, rzrq, tsgzzjhm, tsgzzjyxq, tsgzzjfsrq, bzrq, sj, xfzt);

                                        //记录插入成功时，更新Excel“备注”字段提示：数据合格：已入库
                                        xSt.Cells[j + 2, 20] = "数据合格：已入库(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                        //合格记录从1到21列，全部都是黄绿底，黑字
                                        Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                        range.Interior.ColorIndex = 35;//背景的颜色：黄绿色
                                        range.Font.ColorIndex = 1;//字的颜色：黑色

                                        //日志记录
                                        commdata.WriteLogFile("1（数据合格入库）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                    }
                                    else
                                    {
                                        //身份证已查到，但是备注字段没有标识“已入库”，需要去数据库中查一下，从而判断该记录是不是已操作过
                                        System.Data.DataTable szfhhasmore = useradp.GetDataBy_XM_SFZH(xm, sfzhm);
                                        if (szfhhasmore.Rows.Count == 1)
                                        {
                                            string bmmc = "";
                                            System.Data.DataTable bmdt = bmadp.GetDataBy_DEPT_id(int.Parse(szfhhasmore.Rows[0]["DEPT_id"].ToString()));

                                            //用Excel中的姓名和身份证号去数据库查，有唯一记录时，更新Excel“备注”字段提示：身份证号系统已经存在，请勿重复添加
                                            xSt.Cells[j + 2, 20] = tempbz + " 身份证号已经存在-" + bmdt.Rows[0]["BMMC"].ToString() + "，请勿重复添加(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                            //不合格记录从1到21列，全部都是白底红字
                                            Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                            range.Interior.ColorIndex = 2;//背景的颜色：白色
                                            range.Font.ColorIndex = 3;//字的颜色：红色                                              

                                            //日志记录
                                            commdata.WriteLogFile("2（身份证号已经存在-" + bmdt.Rows[0]["BMMC"].ToString() + "，请勿重复添加）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                        }
                                        else
                                        {
                                            //用Excel中的姓名和身份证号去数据库查，有多条记录时，更新Excel“备注”字段提示：身份证号与他人重复，请核对信息
                                            //验证未通过，有不合格数据，不合格信息写在“备注”字段内
                                            xSt.Cells[j + 2, 20] = tempbz + " 身份证号与他人重复，请核对信息并重新提交照片(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                            //不合格记录从1到21列，全部都是白底红字
                                            Excel.Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                            range.Interior.ColorIndex = 2;//背景的颜色：白色
                                            range.Font.ColorIndex = 3;//字的颜色：红色

                                            //日志记录
                                            commdata.WriteLogFile("3（身份证号与他人重复，请核对信息并重新提交照片）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                        }
                                    }
                                }
                                else
                                {
                                    //验证未通过，有不合格数据，不合格信息写在“备注”字段内
                                    xSt.Cells[j + 2, 20] = tempbz;

                                    //不合格记录从1到21列，全部都是白底红字
                                    Excel.Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                    range.Interior.ColorIndex = 2;//背景的颜色：白色
                                    range.Font.ColorIndex = 3;//字的颜色：红色

                                    //日志记录
                                    commdata.WriteLogFile("4（未通过验证）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                }
                            }
                            else
                            {
                                //不管数据是否通过验证，都显示tempbz（验证信息）
                                //Excel中有重复的身份证记录，更新Excel文件“备注”字段内容，数据不进库
                                xSt.Cells[j + 2, 20] = tempbz + " Excel内身份证号重复（需要重新提交身份证号及照片信息）(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ") ";//2005-11-5 14:06 

                                //不合格记录从1到21列，全部都是白底红字
                                Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 21]];
                                range.Interior.ColorIndex = 2;//背景的颜色：白色
                                range.Font.ColorIndex = 3;//字的颜色：红色

                                //日志记录
                                commdata.WriteLogFile("5（Excel内身份证号重复（需要重新提交身份证号及照片信息））：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                            }
                        }
                    }
                    w.Save();
                    w.Close(Missing.Value, Missing.Value, Missing.Value);
                    app.Workbooks.Close();
                    //退出excel
                    app.Quit();
                    app = null;
                }
                catch (Exception ex)
                {
                    //退出excel
                    app.Quit();
                    app = null;
                }
                finally
                {
                    //退出excel
                    app = null;
                }
            }
        }

        /// <summary>
        /// Excel人员及照片验证
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            if (this.textBox2.Text == "")
            {
                MessageBox.Show("请选择照片文件存放的目录!");
                return;
            }

            this.openFileDialog1.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";

            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                Control.CheckForIllegalCrossThreadCalls = false;

                Thread trd = new Thread(new ThreadStart(this.ThreadTask));
                trd.IsBackground = true;
                trd.Start();
            }
        }

        /// <summary>
        /// 验证信息
        /// </summary>
        /// <param name="xm"></param>
        /// <param name="xb"></param>
        /// <param name="sfzhm"></param>
        /// <param name="gsdwqc"></param>
        /// <param name="gsbm"></param>
        /// <param name="rylb"></param>
        /// <param name="gwgz"></param>
        /// <returns></returns>
        public string YZXX(string xm, string xb, string sfzhm, string gsdwqc, string gsbm, string rylb, string gwgz)
        {
            if (xm == null)
            {
                tempbz += " 姓名为空\t";
            }
            else
            {
                if (xm.Length < 1)
                {
                    tempbz += " 姓名不正确\t";
                }

            }

            if (xb == null)
            {
                tempbz += " 性别为空\t";
            }
            else
            {
                if ((xb != "男") && (xb != "女"))
                {
                    tempbz += " 性别不正确\t";
                }
            }

            if (sfzhm == null)
            {
                tempbz += " 身份证号码为空\t";
            }
            else
            {
                if ((sfzhm.Length != 15) && (sfzhm.Length != 18))
                {
                    tempbz += " 身份证号长度不正确\t";
                }
            }

            if (gsdwqc == null)
            {
                tempbz += " 公司单位全称为空\t";
            }
            else
            {
                if (gsdwqc.Length < 1)
                {
                    tempbz += " 公司单位全称不正确\t";
                }
                else
                {
                    //判断公司单位全称
                    System.Data.DataTable groupdt = groupadp.GetDataBy_jgname(gsdwqc);
                    if (groupdt.Rows.Count < 1)
                    {
                        tempbz += " 公司单位全称不匹配";
                    }
                    else
                    {
                        gsdwid = int.Parse(groupdt.Rows[0]["GRPUP_ID"].ToString());//获取公司单位ID
                        System.Data.DataTable bmdt = bmadp.GetDataBy_grpup_id(gsdwid);
                        gsdeptid = int.Parse(bmdt.Rows[0]["DEPT_ID"].ToString());//获取公司单位对应的部门ID
                    }
                }
            }

            if (gsbm == null)
            {
                tempbz += " 归属部门为空\t";
            }
            else
            {
                if (gsbm.Length < 1)
                {
                    tempbz += " 归属部门不正确\t";
                }
                else
                {
                    //判断归属部门
                    System.Data.DataTable gsbmdt = bmadp.GetDataBy_BMMC83(gsbm);
                    if (gsbmdt.Rows.Count < 1)
                    {
                        tempbz += " 归属部门不匹配";
                    }
                    else
                    {
                        gsbmid = int.Parse(gsbmdt.Rows[0]["DEPT_id"].ToString());//获取归属部门ID
                        if (gsbmid == -1)
                            zp = "~/admin/ftp/DBPersonImg/" + xm + sfzhm + ".jpg";//-1：设置存放正式员工存放照片路径
                        else
                            zp = "~/admin/ftp/DBFZSPersonImg/" + xm + sfzhm + ".jpg";//非-1：设置存放非正式员工照片路径
                    }
                }
            }

            if (rylb == null)
            {
                tempbz += " 人员类别为空\t";
            }
            else
            {
                if (rylb.Length < 1)
                {
                    tempbz += " 人员类别不正确\t";
                }
                else
                {
                    //判断人员类别
                    System.Data.DataTable rylbdt = usertypeadp.GetDataBy_RY_Name(rylb);
                    if (rylbdt.Rows.Count < 1)
                    {
                        tempbz += " 人员类别不匹配";
                    }
                    else
                    {
                        rylbid = int.Parse(rylbdt.Rows[0]["RY_ID"].ToString());//获取人员类别ID
                    }
                }
            }

            if (gwgz == null)
            {
                tempbz += " 岗位工种为空\t";
            }
            else
            {
                if (gwgz.Length < 1)
                {
                    tempbz += " 岗位工种不正确\t";
                }
                else
                {
                    //判断岗位工种
                    System.Data.DataTable gwgzdt = detailadp.GetDataBy_MC(gwgz);
                    if (gwgzdt.Rows.Count < 1)
                    {
                        tempbz += " 岗位工种不匹配";
                    }
                    else
                    {
                        gwgzid = int.Parse(gwgzdt.Rows[0]["DETAIL_ID"].ToString());//获取岗位工种ID
                    }
                }
            }

            //查询照片文件是否存在，存在写入照片存在，不存在提示照片不存在           
            if (this.textBox2.Text.Trim() != "")
                pathName = this.textBox2.Text.Trim() + "/";
            pathFileName = pathName + xm + sfzhm + ".jpg";

            if (!File.Exists(pathFileName))
            {
                tempbz += " 照片信息不匹配,需要重新提交照片";
            }
            return tempbz;
        }

        /// <summary>
        /// 删除已入库的照片和Excel记录
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button5_Click(object sender, EventArgs e)
        {
            //循环Excel文件，去照片目录下读取照片信息。要是成功匹配，修改Excel第21列数据。不成功提示。
            //加载Excel文件到DataTable中
            System.Data.DataTable tb = JZExcelFile();

            if (tb != null)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    int j = i;//用来标识Excel中和记录是第几条
                    int xfzt = 0;
                    tempbz = "数据不正确";//数据验证标识

                    //必填数据
                    string yggh = tb.Rows[i]["员工工号"].ToString();
                    string xm = Regex.Replace(tb.Rows[i]["姓名"].ToString(), @"\s", "");
                    string xb = Regex.Replace(tb.Rows[i]["性别"].ToString(), @"\s", "");
                    string sfzhm = Convert.ToString(Regex.Replace(tb.Rows[i]["身份证号码"].ToString(), @"\s", ""));
                    string gsdwqc = Regex.Replace(tb.Rows[i]["公司单位全称"].ToString(), @"\s", "");
                    string gsbm = Regex.Replace(tb.Rows[i]["归属部门"].ToString(), @"\s", "");
                    string rylb = Regex.Replace(tb.Rows[i]["人员类别"].ToString(), @"\s", "");
                    string gwgz = Regex.Replace(tb.Rows[i]["岗位工种"].ToString(), @"\s", "");
                    gsdw = gsdwqc;
                    string bz = tb.Rows[i]["备注"].ToString();


                    //查询照片文件是否存在，存在写入照片存在，不存在提示照片不存在
                    string pathName = System.Windows.Forms.Application.StartupPath + "/img/DBFZSPersonImg/";
                    if (this.textBox2.Text.Trim() != "")
                        pathName = this.textBox2.Text.Trim() + "/";
                    string pathFileName = pathName + xm + sfzhm + ".jpg";

                    System.Data.DataTable szfhhasmore = useradp.GetDataBy_XM_SFZH(xm, sfzhm);
                    if (szfhhasmore.Rows.Count == 1)
                    {
                        if (File.Exists(pathFileName))
                        {
                            File.Delete(pathFileName);
                        }              
                    }
 

                }
            }
        }

        /// <summary>
        /// Excel人员及照片验证及入库,入库后删除Excel记录和照片记录
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button6_Click(object sender, EventArgs e)
        {
            if (this.textBox2.Text == "")
            {
                MessageBox.Show("请选择照片文件存放的目录!");
                return;
            }

            this.openFileDialog1.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string FileName = this.openFileDialog1.FileName;
                this.textBox1.Text = FileName;

                //循环Excel文件，去照片目录下读取照片信息。要是成功匹配，修改Excel第21列数据。不成功提示。
                //加载Excel文件到DataTable中
                System.Data.DataTable tb = JZExcelFile();

                if (tb != null)
                {
                    string filePath = textBox1.Text;
                    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                    app.Visible = false;

                    //打开Exce
                    Workbook w = (Workbook)app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                    Microsoft.Office.Interop.Excel._Worksheet xSt = (Microsoft.Office.Interop.Excel._Worksheet)w.Worksheets[1];

                    try
                    {
                        int kill = 0;
                        for (int i = 0; i < tb.Rows.Count; i++)
                        {
                            int j = i+2;//用来标识Excel中和记录是第几条
                            if(kill!=0)
                                j = j - kill;
                            int xfzt = 0;
                            tempbz = "数据不正确";//数据验证标识

                            //必填数据
                            string yggh = tb.Rows[i]["员工工号"].ToString();
                            string xm = Regex.Replace(tb.Rows[i]["姓名"].ToString(), @"\s", "");
                            string xb = Regex.Replace(tb.Rows[i]["性别"].ToString(), @"\s", "");
                            string sfzhm = Convert.ToString(Regex.Replace(tb.Rows[i]["身份证号码"].ToString(), @"\s", ""));
                            string gsdwqc = Regex.Replace(tb.Rows[i]["公司单位全称"].ToString(), @"\s", "");
                            string gsbm = Regex.Replace(tb.Rows[i]["归属部门"].ToString(), @"\s", "");
                            string rylb = Regex.Replace(tb.Rows[i]["人员类别"].ToString(), @"\s", "");
                            string gwgz = Regex.Replace(tb.Rows[i]["岗位工种"].ToString(), @"\s", "");
                            gsdw = gsdwqc;

                            //验证能通过就可以的数据
                            string zgzzc = tb.Rows[i]["资格证职称"].ToString();
                            string byyx = tb.Rows[i]["毕业院校"].ToString();
                            string xl = tb.Rows[i]["学历"].ToString();
                            string tsgzzjhm = tb.Rows[i]["特殊工种证件号码"].ToString();
                            string dh = Regex.Replace(tb.Rows[i]["电话"].ToString(), @"\s", "");
                            string sj = Regex.Replace(tb.Rows[i]["手机"].ToString(), @"\s", "");
                            string bz = tb.Rows[i]["备注"].ToString();
                            DateTime sr = DateTime.Parse("1900-01-01");
                            DateTime tjdate = DateTime.Now;

                            int xh = 0;
                            try { xh = tb.Rows[i]["序号"] != null ? int.Parse(tb.Rows[i]["序号"].ToString()) : 0; }
                            catch (Exception ex) { xh = 0; }

                            DateTime byrq = DateTime.Parse("1900-01-01");
                            try { byrq = tb.Rows[i]["毕业日期"] != null ? DateTime.Parse(tb.Rows[i]["毕业日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { byrq = DateTime.Parse("1900-01-01"); }

                            DateTime rzrq = DateTime.Parse("1900-01-01");
                            try { rzrq = tb.Rows[i]["入职日期"] != null ? DateTime.Parse(tb.Rows[i]["入职日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { rzrq = DateTime.Parse("1900-01-01"); }

                            DateTime tsgzzjyxq = DateTime.Parse("1900-01-01");
                            try { tsgzzjyxq = tb.Rows[i]["特殊工种证件有效期"] != null ? DateTime.Parse(tb.Rows[i]["特殊工种证件有效期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { tsgzzjyxq = DateTime.Parse("1900-01-01"); }

                            DateTime tsgzzjfsrq = DateTime.Parse("1900-01-01");
                            try { tsgzzjfsrq = tb.Rows[i]["特殊工种证件复审日期"] != null ? DateTime.Parse(tb.Rows[i]["特殊工种证件复审日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { tsgzzjfsrq = DateTime.Parse("1900-01-01"); }

                            DateTime bzrq = DateTime.Parse("1900-01-01");
                            try { bzrq = tb.Rows[i]["办证日期"] != null ? DateTime.Parse(tb.Rows[i]["办证日期"].ToString()) : DateTime.Parse("1900-01-01"); }
                            catch (Exception ex) { bzrq = DateTime.Parse("1900-01-01"); }

                            //必填项，数据开始验证
                            tempbz = YZXX(xm, xb, sfzhm, gsdwqc, gsbm, rylb, gwgz);

                            //查询照片文件是否存在，存在写入照片存在，不存在提示照片不存在
                            string pathName = System.Windows.Forms.Application.StartupPath + "/img/DBFZSPersonImg/";
                            if (this.textBox2.Text.Trim() != "")
                                pathName = this.textBox2.Text.Trim();
                            string serverFilename = xm + sfzhm + ".jpg";
                            FileInfo fileinfo = new FileInfo(pathName + "/" + serverFilename);

                            if (bz.Contains("已入库"))
                            {
                                //已入库的记录，目前不用处理
                            }
                            else
                            {
                                //信息验证通过，有对应照片，数据库中没有身份证方可以通过，入库
                                //判断提交的Excel表格中是不是有重复的身份证号码
                                int sfznum = tb.Select("身份证号码 = '" + sfzhm + "'").Count();
                                if (sfznum < 2)
                                {
                                    //Excel中只有唯一的一条记录时
                                    if (tempbz == "数据不正确")//表示验证通过
                                    {

                                        //去数据库查询，该身份证号是否存在，若身份证匹配，就进行下一条记录
                                        System.Data.DataTable dt = useradp.GetDataBy_sfzh(sfzhm);
                                        if (dt.Rows.Count < 1)
                                        {
                                            //身份证无匹配信息，就新增一条记录

                                            //先做FTP上传照片文件
                                            int aa = Common.FileTran.SaveFile(fileinfo, serverFilename, "DBFZSPersonImg", "");
                                            if (!(aa == 0))
                                            {
                                                MessageBox.Show("FTP服务器连接失败，请检查网络连接！");
                                                commdata.WriteLogFile("FTP服务器连接失败，请检查网络连接！");

                                                w.Save();
                                                w.Close(Missing.Value, Missing.Value, Missing.Value);
                                                app.Workbooks.Close();

                                                app.Quit();
                                                app = null;
                                                return;
                                            }

                                            //获取人员编号
                                            string rybh = commdata.getRybh(gsdwqc, gwgz);

                                            //数据通过合法的验证，可以在T_USER表插入数据
                                            useradp.Insert(gsdeptid, xm, xm, xh, "全局访问", xb, sr, dh, "123456", "", gsdwqc, "", "", zp, "不停用", 0, gsbmid + "", rylbid, sfzhm, rybh, tjdate, gwgzid, 200, 0, "", "卡加指纹", "-100", "", byyx, byrq, xl, rzrq, tsgzzjhm, tsgzzjyxq, tsgzzjfsrq, bzrq, sj, xfzt);

                                            //记录插入成功时，更新Excel“备注”字段提示：数据合格：已入库
                                            xSt.Cells[j, 20] = "数据合格：已入库(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                            //合格记录从1到21列，全部都是黄绿底，黑字
                                            Range range = app.Range[app.Cells[j, 1], app.Cells[j, 21]];
                                            range.Interior.ColorIndex = 35;//背景的颜色：黄绿色
                                            range.Font.ColorIndex = 1;//字的颜色：黑色

                                            //日志记录
                                            commdata.WriteLogFile("1（数据合格入库）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                        }
                                        else
                                        {
                                            //身份证已查到，但是备注字段没有标识“已入库”，需要去数据库中查一下，从而判断该记录是不是已操作过
                                            System.Data.DataTable szfhhasmore = useradp.GetDataBy_XM_SFZH(xm, sfzhm);
                                            if (szfhhasmore.Rows.Count == 1)
                                            {
                                                string bmmc = "";
                                                System.Data.DataTable bmdt =bmadp.GetDataBy_DEPT_id(int.Parse(szfhhasmore.Rows[0]["DEPT_id"].ToString()));

                                                //用Excel中的姓名和身份证号去数据库查，有唯一记录时，更新Excel“备注”字段提示：身份证号系统已经存在，请勿重复添加
                                                xSt.Cells[j, 20] = tempbz + " 身份证号已经存在(" + bmdt.Rows[0]["BMMC"].ToString() + ")，请勿重复添加(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                                //不合格记录从1到21列，全部都是白底红字
                                                Range range = app.Range[app.Cells[j, 1], app.Cells[j, 21]];
                                                range.Interior.ColorIndex = 2;//背景的颜色：白色
                                                range.Font.ColorIndex = 3;//字的颜色：红色

                                                ///////=============================================================================
                                                //查询照片文件是否存在，存在写入照片存在，不存在提示照片不存在
                                                if (this.textBox2.Text.Trim() != "")
                                                    pathName = this.textBox2.Text.Trim() + "/";
                                                string pathFileName = pathName + xm + sfzhm + ".jpg";


                                                //string destFileName = System.Windows.Forms.Application.StartupPath + "/img/DBFZSPersonImgTemp/"+ xm + sfzhm + ".jpg";
                                                if (File.Exists(pathFileName))
                                                {
                                                    //img/DBFZSPersonImg/                                                        
                                                    //C:\Documents and Settings\Administrator\桌面\20130718\pic\新建文件夹\余昌飞441223197708212333.jpg
                                                    //将文件移动到DEBUG下的img/DBFZSPersonImgTemp/目录下，删除选中文件夹下的照片
                                                    // File.Move(pathFileName, destFileName);
                                                    File.Delete(pathFileName);
                                                }
                                                range = (Excel.Range)app.Rows[j, Missing.Value];
                                                range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
                                                kill++;
                                                ///////=============================================================================

                                                //日志记录
                                                commdata.WriteLogFile("2（身份证号已经存在，请勿重复添加，Excel记录、照片删除）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                            }
                                            else
                                            {
                                                //用Excel中的姓名和身份证号去数据库查，有多条记录时，更新Excel“备注”字段提示：身份证号与他人重复，请核对信息
                                                //验证未通过，有不合格数据，不合格信息写在“备注”字段内
                                                xSt.Cells[j, 20] = tempbz + " 身份证号与他人重复，请核对信息并重新提交照片(" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                                //不合格记录从1到21列，全部都是白底红字
                                                Excel.Range range = app.Range[app.Cells[j, 1], app.Cells[j, 21]];
                                                range.Interior.ColorIndex = 2;//背景的颜色：白色
                                                range.Font.ColorIndex = 3;//字的颜色：红色

                                                //日志记录
                                                commdata.WriteLogFile("3（身份证号与他人重复，请核对信息并重新提交照片）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                            }
                                        }
                                    }
                                    else
                                    {
                                        //验证未通过，有不合格数据，不合格信息写在“备注”字段内
                                        xSt.Cells[j, 20] = tempbz;

                                        //不合格记录从1到21列，全部都是白底红字
                                        Excel.Range range = app.Range[app.Cells[j, 1], app.Cells[j, 21]];
                                        range.Interior.ColorIndex = 2;//背景的颜色：白色
                                        range.Font.ColorIndex = 3;//字的颜色：红色

                                        //日志记录
                                        commdata.WriteLogFile("4（未通过验证）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                    }
                                }
                                else
                                {
                                    //Excel中有重复的身份证记录，更新Excel文件“备注”字段内容，数据不进库
                                    xSt.Cells[j, 20] = tempbz + " 身份证号重复（需要重新提交身份证号及照片信息）" + DateTime.Now.GetDateTimeFormats('g')[0].ToString() + ")";//2005-11-5 14:06 

                                    //不合格记录从1到21列，全部都是白底红字
                                    Range range = app.Range[app.Cells[j, 1], app.Cells[j, 21]];
                                    range.Interior.ColorIndex = 2;//背景的颜色：白色
                                    range.Font.ColorIndex = 3;//字的颜色：红色

                                    //日志记录
                                    commdata.WriteLogFile("5（Excel内身份证号重复）：\t" + xm + "\t" + sfzhm + "\t" + tempbz);
                                }
                            }
                        }
                        w.Save();
                        w.Close(Missing.Value, Missing.Value, Missing.Value);
                        app.Workbooks.Close();
                        //退出excel
                        app.Quit();
                        app = null;
                    }
                    catch (Exception ex)
                    {
                        w.Save();
                        w.Close(Missing.Value, Missing.Value, Missing.Value);
                        app.Workbooks.Close();
                        //退出excel
                        app.Quit();
                        app = null;
                    }
                    finally
                    {
                        //退出excel
                        app = null;
                    }
                }
            }
        }

        /// <summary>
        /// 照片改名字
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button7_Click(object sender, EventArgs e)
        {
            if (this.folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                string select2jpath = this.folderBrowserDialog1.SelectedPath;
                System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(select2jpath);

                FileInfo[] ff = di.GetFiles("*.*");//只取jpg
                string ss = "";//存放内容
                foreach (FileInfo temp in ff)
                {
                    string name = temp.Name;//周清132903197512028113.jpg
                    string fullname = temp.FullName;//E:\hx\一卡通\卡务中心\kwzx\kwzx\bin\Debug\zp\周清132903197512028113.jpg
                    string driectoryname = temp.DirectoryName;//E:\hx\一卡通\卡务中心\kwzx\kwzx\bin\Debug\zp
                    string tempname = "";
                    if (name.Length > 22)
                    {
                        tempname = (name.Substring(name.LastIndexOf("\\") + 1, name.LastIndexOf("."))).Substring(8);
                        tempname = (Regex.Replace(tempname, @"\s", "")).Replace("_",""); 
                    }
                    else
                    {
                        tempname = name.Substring(name.LastIndexOf("\\") + 1, name.LastIndexOf(".") - name.LastIndexOf("\\") - 1);
                    }
 
                    string replaceWith = tempname + ".jpg";
                    //查询一下，有没有重名的，要是有重名的话就+1
                    int gg = 0;
                    FileInfo[] fileinfo = di.GetFiles("*.jpg");//只取jpg
                    foreach (FileInfo temp1 in fileinfo)
                    {
                        if (temp1.Name == replaceWith)
                        {
                            replaceWith = name;
                        }
                    }
                    renameFiles(select2jpath, name, replaceWith);
                }                
            }

        }

        private static void renameFiles(string folderPath, string findString, string replaceWith)
        {
            bool ret = false;
            try
            {
                DirectoryInfo di = new DirectoryInfo(folderPath);
                string destFileName = "";
                foreach (FileInfo fi in di.GetFiles())
                {
                    if (fi.Name.IndexOf(findString) > -1)
                    {
                        destFileName = fi.DirectoryName + "\\" + fi.Name.Replace(findString, replaceWith);
                        File.Move(fi.FullName, destFileName);
                    }
                }
                ret = true;
            }
            catch (System.Exception ex)
            {
                throw new ApplicationException("Error in renameFiles", ex);
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            DataSet1TableAdapters.V_user_cardTableAdapter v_user_card_adp = new DataSet1TableAdapters.V_user_cardTableAdapter();
            this.openFileDialog1.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";
             if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
             {
                 string FileName = this.openFileDialog1.FileName;
                 string sfzsyg = "正式员工";

                 System.Data.DataTable dt = null;
                 try
                 {
                     //获得文件名
                     string path = FileName;
                     //创建一个数据链接 
                     string strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + " ;Extended Properties='Excel 8.0;HDR=False;IMEX=1;'";
                     OleDbConnection myConn = new OleDbConnection(strCon);
                     // string strCom = " SELECT 人员编号,姓名,性别,电话,身份证号,所属部门,公司,照片,工种 FROM [Sheet1$] ";
                     string strCom = " SELECT 房间号,姓名,所属部门单位,门锁类型,人员编号,备注 FROM [Sheet1$] ";
                     myConn.Open();
                     //打开数据链接，得到一个数据集
                     OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                     //创建一个 DataSet对象 
                     DataSet myDataSet = new DataSet();
                     //得到自己的DataSet对象 
                     myCommand.Fill(myDataSet, "[Sheet1$]");

                     //关闭此数据链接 
                     myConn.Close();
                     dt = myDataSet.Tables[0];

                 }
                 catch (Exception ex)
                 {
                     MessageBox.Show("Excel文件表头字段不对！");
                     return;
                 }

                 if (dt != null)
                 {
                     string filePath = FileName;
                     Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                     app.Visible = false;

                     //打开Exce
                     Workbook w = (Workbook)app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value,
                          Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                          Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                     Microsoft.Office.Interop.Excel._Worksheet xSt = (Microsoft.Office.Interop.Excel._Worksheet)w.Worksheets[1];

                     try
                     {
                         int kill = 0;
                         for (int i = 0; i < dt.Rows.Count; i++)
                         {
                             int j = i;
                             if (kill != 0)
                                 j = j - kill;
                             string fjh = dt.Rows[i]["房间号"].ToString();
                             string ryxm = Regex.Replace(dt.Rows[i]["姓名"].ToString(), @"\s", "");
                             //string rybh = dt.Rows[i]["人员编号"].ToString();
                             
                             if((fjh=="")||(ryxm=="")){
                             }else{
                                 //去查询库里人员是否存在，并只有一人。
                                 //有一个人，则备注说明 用户是否是正式，所在单位
                                 System.Data.DataTable dtuser = v_user_card_adp.GetDataBy_XM(ryxm);
                                if (dtuser.Rows.Count >1)
                                {
                                    //多条记录对应成功
                                    for (int k = 0; k < dtuser.Rows.Count; k++)
                                    {
                                        if (int.Parse(dtuser.Rows[k]["UBY"].ToString()) > -1)
                                        {
                                            sfzsyg = "非正式员工";
                                        }
                                        else
                                        {
                                            sfzsyg = "正式员工";
                                        }
                                        xSt.Cells[j + 2, 6 + k] = sfzsyg +  "-" + dtuser.Rows[k]["BMMC"] + "-" + dtuser.Rows[k]["rybh"];
                                        //Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 3 + k]];
                                        //range.Font.ColorIndex = 3;//字的颜色：红色

                                    }
                                }
                                else if (dtuser.Rows.Count == 1)
                                {
                                    if (int.Parse(dtuser.Rows[0]["UBY"].ToString()) > -1)
                                    {
                                        sfzsyg = "非正式员工";
                                        //只有一条记录对应成功
                                        xSt.Cells[j + 2, 6] = sfzsyg + "-" + dtuser.Rows[0]["BMMC"] + "-" + dtuser.Rows[0]["rybh"];
                                        xSt.Cells[j + 2, 4] = dtuser.Rows[0]["rybh"];
                                        xSt.Cells[j + 2, 5] = dtuser.Rows[0]["BMMC"];
                                        //合格记录从1到21列，全部都是黄绿底，黑字
                                        Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 5]];
                                        // range.Interior.ColorIndex = 35;//背景的颜色：黄绿色
                                        range.Font.ColorIndex = 1;//字的颜色：黑色
                                    }
                                    else
                                    {
                                        //删除正式员工信息
                                        //Range range = (Excel.Range)app.Rows[j+2, Missing.Value];
                                        //range.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
                                        //kill++;
                                    }
                                }
                                else
                                {
                                    //没查到，提示没有该人员记录
                                    xSt.Cells[j + 2, 6] = "没有查到该人员记录";
                                    Range range = app.Range[app.Cells[j + 2, 1], app.Cells[j + 2, 3]];
                                    range.Font.ColorIndex = 3;//字的颜色：红色
                                }
                             }

                         }
                         w.Save();
                         w.Close(Missing.Value, Missing.Value, Missing.Value);
                         app.Workbooks.Close();
                         //退出excel
                         app.Quit();
                         app = null;
                     }
                     catch (Exception ex)
                     {
                         w.Save();
                         w.Close(Missing.Value, Missing.Value, Missing.Value);
                         app.Workbooks.Close();
                         //退出excel
                         app.Quit();
                         app = null;
                     }
                     finally
                     {
                         //退出excel
                         app = null;
                     }
                 }
             }
        }

        private void button9_Click(object sender, EventArgs e)
        {
            DataSet1TableAdapters.T_USERTableAdapter t_user_adp = new DataSet1TableAdapters.T_USERTableAdapter();

            this.openFileDialog1.Filter = "*.xls|*.xls|所有文件(*.*)|*.*";
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string FileName = this.openFileDialog1.FileName;
                System.Data.DataTable dt = null;
                try
                {
                    //获得文件名
                    string path = FileName;
                    //创建一个数据链接 
                    string strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + " ;Extended Properties='Excel 8.0;HDR=False;IMEX=1;'";
                    OleDbConnection myConn = new OleDbConnection(strCon);
                    // string strCom = " SELECT 人员编号,姓名,性别,电话,身份证号,所属部门,公司,照片,工种 FROM [Sheet1$] ";
                    string strCom = " SELECT 房间号,姓名,所属部门单位,门锁类型,人员编号,备注 FROM [Sheet1$] ";
                    myConn.Open();
                    //打开数据链接，得到一个数据集
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                    //创建一个 DataSet对象 
                    DataSet myDataSet = new DataSet();
                    //得到自己的DataSet对象 
                    myCommand.Fill(myDataSet, "[Sheet1$]");

                    //关闭此数据链接 
                    myConn.Close();
                    dt = myDataSet.Tables[0];

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Excel文件表头字段不对！");
                    return;
                }

                if (dt != null)
                {
                    string filePath = FileName;
                    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                    app.Visible = false;

                    //打开Exce
                    Workbook w = (Workbook)app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                         Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                    Microsoft.Office.Interop.Excel._Worksheet xSt = (Microsoft.Office.Interop.Excel._Worksheet)w.Worksheets[1];

                    try
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            string fjh = Regex.Replace(dt.Rows[i]["房间号"].ToString(), @"\s", "");
                            string ryxm = Regex.Replace(dt.Rows[i]["姓名"].ToString(), @"\s", "");

                            if ((fjh == "") || (ryxm == ""))
                            {
                            }
                            else
                            {
                                //去查询库里人员是否存在，并只有一人。
                                //有一个人，则备注说明 用户是否是正式，所在单位
                                System.Data.DataTable dtuser = t_user_adp.GetDataBy_XM(ryxm);
                                if (dtuser.Rows.Count > 1)
                                {
                                    string temp_xst = "";
                                    //多条记录
                                    for (int k = 0; k < dtuser.Rows.Count; k++)
                                    {                                        
                                        temp_xst += dtuser.Rows[k]["dz"] + "-" + dtuser.Rows[k]["rybh"] + ":";                                       
                                    }
                                    xSt.Cells[i + 2, 6] += temp_xst;
                                    Range range = app.Range[app.Cells[i + 2, 6], app.Cells[i + 2, 6]];
                                    range.Font.ColorIndex = 3;//字的颜色：红色
                                }else if(dtuser.Rows.Count==1){
                                    //一条记录
                                    xSt.Cells[i + 2, 5] = dtuser.Rows[0]["rybh"];
                                    xSt.Cells[i + 2, 6] = dtuser.Rows[0]["dz"];
                                    //合格记录从1到21列，全部都是黄绿底，黑字
                                    Range range = app.Range[app.Cells[i + 2, 5], app.Cells[i + 2, 6]];
                                    // range.Interior.ColorIndex = 35;//背景的颜色：黄绿色
                                    range.Font.ColorIndex = 1;//字的颜色：黑色
                                }
                                else if (dtuser.Rows.Count < 1)
                                {
                                    //未查到记录
                                    xSt.Cells[i + 2, 5] = "没有查到该人员记录";
                                    Range range = app.Range[app.Cells[i + 2, 5], app.Cells[i + 2, 5]];
                                    range.Font.ColorIndex = 3;//字的颜色：红色
                                }                     
                            }
                        }
                        w.Save();
                        w.Close(Missing.Value, Missing.Value, Missing.Value);
                        app.Workbooks.Close();
                        //退出excel
                        app.Quit();
                        app = null;
                    }
                    catch (Exception ex)
                    {
                        w.Save();
                        w.Close(Missing.Value, Missing.Value, Missing.Value);
                        app.Workbooks.Close();
                        //退出excel
                        app.Quit();
                        app = null;
                    }
                    finally
                    {
                        //退出excel
                        app = null;
                    }
                }
            }
        }
    }
}
